package util;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;



/**
 *common database DAO
 * @author vsked
 */
public class UtilDAO {

    private static Connection conn = null;
    private static PreparedStatement pt = null;
    private static boolean flag = false;
    private static String sql = "";

    /**
     * @param inTableName table
     * @param inKey primary key name
     * @param inId  
     * @return
     */
    public static boolean del(String inTableName,String inKey, String inId) {
        try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "delete from " + inTableName + " where "+inKey+"=?";
            pt = conn.prepareStatement(sql);
            pt.setString(1, inId);
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeSqlConnection();
        }
        return flag;
    }
    
    public static boolean excuteSQL(String inSql) {
        try {
            conn = ConnectDB.getSqlServerConnection();
            sql = inSql;
            pt = conn.prepareStatement(sql);
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeSqlConnection();
        }
        return flag;
    }
    
    public static boolean excuteSQL(Connection inConn,String inSql) {
        try {
            conn = inConn;
            sql = inSql;
            pt = conn.prepareStatement(sql);
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeSqlConnection();
        }
        return flag;
    }
    
    public static boolean del(Connection inConn,String inTableName,String inKey, String inId) {
        try {
            conn = inConn;
            sql = "delete from " + inTableName + " where "+inKey+"=?";
            pt = conn.prepareStatement(sql);
            pt.setString(1, inId);
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
    
    /**
     * @param inTableName table
     * @param inKey primary key
     * @param inId  
     * @return
     */
    public static boolean del(String inTableName,String inKey, int inId) {
        try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "delete from " + inTableName + " where "+inKey+"=?";
            pt = conn.prepareStatement(sql);
            pt.setInt(1, inId);
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeSqlConnection();
        }
        return flag;
    }
    
    public static boolean del(Connection inConn,String inTableName,String inKey, int inId) {
        try {
            conn = inConn;
            sql = "delete from " + inTableName + " where "+inKey+"=?";
            pt = conn.prepareStatement(sql);
            pt.setInt(1, inId);
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } 
        return flag;
    }
    
    /**
     * delete all record from table
     * @param inTableName
     * @return true or false
     */
    public static boolean delAll(String inTableName) {
        try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "delete from " + inTableName;
            pt = conn.prepareStatement(sql);
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeSqlConnection();
        }
        return flag;
    }
    public static boolean delAll(Connection inConn,String inTableName) {
        try {
            conn = inConn;
            sql = "delete from " + inTableName;
            pt = conn.prepareStatement(sql);
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
    /**
     * 
     *@author qingyu zhang
     *@function:get the recoder of the mms by condition
     * @param inTableName :the name of table
     * @param inKey:the primary key of table
     * @param order: the order of table
     * @param tiaojian:the condition of the table
     * @return
     *Feb 9, 2011
     *9:05:02 AM
     *vskWebFrameWork
     *util
     *String
     */
    public static String getList(String inTableName,
			String order,String tiaojian) {
		ArrayList lst = new ArrayList();
		try {
			
			
			if(!order.equals(""))
			{
				if(!tiaojian.equals(""))
				{
					sql="select * from "+inTableName+" where "+tiaojian+" order by "+order +" desc";
				}else{
					sql = "select * from "+inTableName+" order by "+order +" desc";
				}
			}else{
				if(!tiaojian.equals(""))
				{
					sql = "select * from "+inTableName+" where "+tiaojian;
				}else{
					sql="select * from "+inTableName;
				}
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return sql;
	}
    /**
     * 
     *@author qingyu zhang
     *@function:get the recoder of the mms by condition
     * @param inTableName :the name of table
     * @param inKey:the primary key of table
     * @param order: the order of table
     * @param tiaojian:the condition of the table
     * @return
     *Feb 9, 2011
     *9:05:02 AM
     *vskWebFrameWork
     *util
     *String
     */
	public static String getListT(String inTableName,
		String order,String tiaojian) {
		ArrayList lst = new ArrayList();
		try {
			if(!order.equals(""))
			{
				if(!tiaojian.equals(""))
				{
					sql="select * from "+inTableName+" where "+tiaojian+" order by "+order;
				}else{
					sql = "select * from "+inTableName+" order by "+order;
				}
			}else{
				if(!tiaojian.equals(""))
				{
					sql = "select * from "+inTableName+" where "+tiaojian;
				}else{
					sql="select * from "+inTableName;
				}
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return sql;
	}
	/**
	 * 
	 *@author qingyu zhang
	 *@function:get the object by the primary key  
	 * @param inTableName:the name of table
	 * @param inKey:the primary key of table
	 * @param id:the value of primary key
	 * @return
	 *Feb 9, 2011
	 *9:10:10 AM
	 *vskWebFrameWork
	 *util
	 *String
	 */
	public static String getObject(String inTableName, String inKey,int id){
		sql = "select * from "+inTableName +" where "+inKey+"="+id;
		return sql;
	}

	/**
	 * 
	 * @param inTableName
	 * @param inColumn
	 * @return
	 */
	public static boolean updateAll(String inTableName,String inColumn) {
        try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "update " + inTableName+" set "+inColumn;
            pt = conn.prepareStatement(sql);
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeSqlConnection();
        }
        return flag;
    }
	/**
	 * 
	 * @param inTableName
	 * @param inColumn update columnString
	 * @param inPrimarykey primary key
	 * @return
	 */
	public static boolean updateAll(String inTableName,String inColumn,String inPrimarykey) {
        try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "update " + inTableName+" set "+inColumn+" where "+inPrimarykey;
            pt = conn.prepareStatement(sql);
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeSqlConnection();
        }
        return flag;
    }
	public static boolean updateAll(Connection inConn,String inTableName,String inColumn,String inPrimarykey) {
        try {
            conn = inConn;
            sql = "update " + inTableName+" set "+inColumn+" where "+inPrimarykey;
            pt = conn.prepareStatement(sql);
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
	
	/**
	 * 截取字符
	 */
	public static String  sub_String(String str)
	{
		String lastString = "";
		if(str.length()>20)
		{
			lastString = str.substring(0, 20);
		}else
		{
			lastString = str;
		}
		return lastString;
	}
	
	/**
	 * del some id
	 * @param inTableName
	 * @param inKey
	 * @param inId
	 * @return
	 */
	public static boolean delin(String inTableName,String inKey, String inId) {
        try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "delete from " + inTableName + " where "+inKey+" in ("+inId+")";
            pt = conn.prepareStatement(sql);
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeSqlConnection();
        }
        return flag;
    }
	public static boolean delinForStringPrimaryKey(String inTableName,String inKey, String inId) {
        try {
        	String[] tempArray=inId.split(",");
        	inId="";
        	for(int i=0;i<tempArray.length;i++)
        		inId="".equals(inId)?"'"+tempArray[i]+"'":inId+",'"+tempArray[i]+"'";
            conn = ConnectDB.getSqlServerConnection();
            sql = "delete from " + inTableName + " where "+inKey+" in ("+inId+")";
            pt = conn.prepareStatement(sql);
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeSqlConnection();
        }
        return flag;
    }
	
	public static boolean delin(Connection inConn,String inTableName,String inKey, String inId) {
        try {
            conn = inConn;
            sql = "delete from " + inTableName + " where "+inKey+" in ("+inId+")";
            pt = conn.prepareStatement(sql);
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return flag;
    }
	public static void main(String[] args) {
		delinForStringPrimaryKey("table_name", "primarykeys", "1,2,3");
	}
	public static String decodeDate(){
		Date date = new Date(); 
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		return sdf.format(date);
	}
}//end class
